Home Explore Help
Register Sign In
J1X
/
huginn
Watch 2
Star 0
Fork 0

Browse Source
Merge pull request #414 from knu/set_charset_for_mysql

Set charset/collation properly for each text column if using MySQL.

Akinori MUSHA 10 years ago
parent
4c938f14e5 857b8ea969
commit
ac7cd8ce5e
6 changed files with 311 additions and 38 deletions
Show Diff Stats
  1. 2 1
      Gemfile.lock
  2. 3 0
      config/initializers/ar_mysql_column_charset.rb
  3. 74 0
      db/migrate/20140813110107_set_charset_for_mysql.rb
  4. 37 37
      db/schema.rb
  5. 110 0
      lib/ar_mysql_column_charset.rb
  6. 85 0
      lib/prepend.rb

+ 2 - 1
Gemfile.lock
View File 

@@ -45,9 +45,10 @@ GEM
45 45 
       extlib (>= 0.9.15)
46 46 
       multi_json (>= 1.0.0)
47 47 
     bcrypt (3.1.7)
48 
-    better_errors (1.1.0)
48 
+    better_errors (2.0.0)
49 49 
       coderay (>= 1.0.0)
50 50 
       erubis (>= 2.6.6)
51 
+      rack (>= 0.9.0)
51 52 
     binding_of_caller (0.7.2)
52 53 
       debug_inspector (>= 0.0.1)
53 54 
     bootstrap-kaminari-views (0.0.3)

+ 3 - 0
config/initializers/ar_mysql_column_charset.rb
View File 

@@ -0,0 +1,3 @@
1 
+ActiveSupport.on_load :active_record do
2 
+  require 'ar_mysql_column_charset'
3 
+end

+ 74 - 0
db/migrate/20140813110107_set_charset_for_mysql.rb
View File 

@@ -0,0 +1,74 @@
1 
+class SetCharsetForMysql < ActiveRecord::Migration
2 
+  def all_models
3 
+    @all_models ||= [
4 
+      Agent,
5 
+      AgentLog,
6 
+      Contact,
7 
+      Event,
8 
+      Link,
9 
+      Scenario,
10 
+      ScenarioMembership,
11 
+      User,
12 
+      UserCredential,
13 
+      Delayed::Job,
14 
+    ]
15 
+  end
16 
+
17 
+  def change
18 
+    conn = ActiveRecord::Base.connection
19 
+
20 
+    # This is migration is for MySQL only.
21 
+    return unless conn.is_a?(ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter)
22 
+
23 
+    reversible do |dir|
24 
+      dir.up do
25 
+        all_models.each { |model|
26 
+          table_name = model.table_name
27 
+
28 
+          # `contacts` may not exist
29 
+          next unless connection.table_exists? table_name
30 
+
31 
+          model.columns.each { |column|
32 
+            name = column.name
33 
+            type = column.type
34 
+            limit = column.limit
35 
+            options = {
36 
+              limit: limit,
37 
+              null: column.null,
38 
+              default: column.default,
39 
+            }
40 
+
41 
+            case type
42 
+            when :string, :text
43 
+              options.update(charset: 'utf8', collation: 'utf8_unicode_ci')
44 
+              case name
45 
+              when 'username'
46 
+                options.update(limit: 767 / 4, charset: 'utf8mb4', collation: 'utf8mb4_unicode_ci')
47 
+              when 'message', 'options', 'name', 'memory',
48 
+                   'handler', 'last_error', 'payload', 'description'
49 
+                options.update(charset: 'utf8mb4', collation: 'utf8mb4_bin')
50 
+              when 'type', 'schedule', 'mode', 'email',
51 
+                   'invitation_code', 'reset_password_token'
52 
+                options.update(collation: 'utf8_bin')
53 
+              when 'guid', 'encrypted_password'
54 
+                options.update(charset: 'ascii', collation: 'ascii_bin')
55 
+              end
56 
+            else
57 
+              next
58 
+            end
59 
+
60 
+            change_column table_name, name, type, options
61 
+          }
62 
+
63 
+          execute 'ALTER TABLE %s CHARACTER SET utf8 COLLATE utf8_unicode_ci' % table_name
64 
+        }
65 
+
66 
+        execute 'ALTER DATABASE %s CHARACTER SET utf8 COLLATE utf8_unicode_ci' % conn.current_database
67 
+      end
68 
+
69 
+      dir.down do
70 
+        # Do nada; no use to go back
71 
+      end
72 
+    end
73 
+  end
74 
+end

+ 37 - 37
db/schema.rb
View File 

@@ -18,7 +18,7 @@ ActiveRecord::Schema.define(version: 20140820003139) do
18 18
19 19 
   create_table "agent_logs", force: true do |t|
20 20 
     t.integer  "agent_id",                      null: false
21 
-    t.text     "message",                       null: false
21 
+    t.text     "message",           limit: 16777215,             null: false, charset: "utf8mb4", collation: "utf8mb4_bin"
22 22 
     t.integer  "level",             default: 3, null: false
23 23 
     t.integer  "inbound_event_id"
24 24 
     t.integer  "outbound_event_id"
 
@@ -28,24 +28,24 @@ ActiveRecord::Schema.define(version: 20140820003139) do
28 28
29 29 
   create_table "agents", force: true do |t|
30 30 
     t.integer  "user_id"
31 
-    t.text     "options"
32 
-    t.string   "type"
33 
-    t.string   "name"
34 
-    t.string   "schedule"
31 
+    t.text     "options",               limit: 16777215,                                charset: "utf8mb4", collation: "utf8mb4_bin"
32 
+    t.string   "type",                                                                                      collation: "utf8_bin"
33 
+    t.string   "name",                                                                  charset: "utf8mb4", collation: "utf8mb4_bin"
34 
+    t.string   "schedule",                                                                                  collation: "utf8_bin"
35 35 
     t.integer  "events_count"
36 36 
     t.datetime "last_check_at"
37 37 
     t.datetime "last_receive_at"
38 38 
     t.integer  "last_checked_event_id"
39 
-    t.datetime "created_at"
40 
-    t.datetime "updated_at"
41 
-    t.text     "memory"
39 
+    t.datetime "created_at",                                               null: false
40 
+    t.datetime "updated_at",                                               null: false
41 
+    t.text     "memory",                limit: 2147483647,                              charset: "utf8mb4", collation: "utf8mb4_bin"
42 42 
     t.datetime "last_web_request_at"
43 43 
     t.integer  "keep_events_for",       default: 0,     null: false
44 44 
     t.datetime "last_event_at"
45 45 
     t.datetime "last_error_log_at"
46 46 
     t.boolean  "propagate_immediately", default: false, null: false
47 47 
     t.boolean  "disabled",              default: false, null: false
48 
-    t.string   "guid",                                  null: false
48 
+    t.string   "guid",                                                     null: false, charset: "ascii",   collation: "ascii_bin"
49 49 
     t.integer  "service_id"
50 50 
   end
51 51 
 
@@ -55,10 +55,10 @@ ActiveRecord::Schema.define(version: 20140820003139) do
55 55 
   add_index "agents", ["user_id", "created_at"], name: "index_agents_on_user_id_and_created_at", using: :btree
56 56
57 57 
   create_table "delayed_jobs", force: true do |t|
58 
-    t.integer  "priority",   default: 0
59 
-    t.integer  "attempts",   default: 0
60 
-    t.text     "handler"
61 
-    t.text     "last_error"
58 
+    t.integer  "priority",                    default: 0
59 
+    t.integer  "attempts",                    default: 0
60 
+    t.text     "handler",    limit: 16777215,                          charset: "utf8mb4", collation: "utf8mb4_bin"
61 
+    t.text     "last_error", limit: 16777215,                          charset: "utf8mb4", collation: "utf8mb4_bin"
62 62 
     t.datetime "run_at"
63 63 
     t.datetime "locked_at"
64 64 
     t.datetime "failed_at"
 
@@ -73,11 +73,11 @@ ActiveRecord::Schema.define(version: 20140820003139) do
73 73 
   create_table "events", force: true do |t|
74 74 
     t.integer  "user_id"
75 75 
     t.integer  "agent_id"
76 
-    t.decimal  "lat",        precision: 15, scale: 10
77 
-    t.decimal  "lng",        precision: 15, scale: 10
78 
-    t.text     "payload"
79 
-    t.datetime "created_at"
80 
-    t.datetime "updated_at"
76 
+    t.decimal  "lat",                           precision: 15, scale: 10
77 
+    t.decimal  "lng",                           precision: 15, scale: 10
78 
+    t.text     "payload",    limit: 2147483647,                                        charset: "utf8mb4", collation: "utf8mb4_bin"
79 
+    t.datetime "created_at",                                              null: false
80 
+    t.datetime "updated_at",                                              null: false
81 81 
     t.datetime "expires_at"
82 82 
   end
83 83 
 
@@ -107,13 +107,13 @@ ActiveRecord::Schema.define(version: 20140820003139) do
107 107 
   add_index "scenario_memberships", ["scenario_id"], name: "index_scenario_memberships_on_scenario_id", using: :btree
108 108
109 109 
   create_table "scenarios", force: true do |t|
110 
-    t.string   "name",                         null: false
111 
-    t.integer  "user_id",                      null: false
110 
+    t.string   "name",                        null: false, charset: "utf8mb4", collation: "utf8mb4_bin"
111 
+    t.integer  "user_id",                     null: false
112 112 
     t.datetime "created_at"
113 113 
     t.datetime "updated_at"
114 
-    t.text     "description"
115 
-    t.boolean  "public",       default: false, null: false
116 
-    t.string   "guid",                         null: false
114 
+    t.text     "description",                              charset: "utf8mb4", collation: "utf8mb4_bin"
115 
+    t.boolean  "public",      default: false, null: false
116 
+    t.string   "guid",                        null: false, charset: "ascii",   collation: "ascii_bin"
117 117 
     t.string   "source_url"
118 118 
     t.string   "tag_bg_color"
119 119 
     t.string   "tag_fg_color"
 
@@ -144,33 +144,33 @@ ActiveRecord::Schema.define(version: 20140820003139) do
144 144 
     t.integer  "user_id",                           null: false
145 145 
     t.string   "credential_name",                   null: false
146 146 
     t.text     "credential_value",                  null: false
147 
-    t.datetime "created_at"
148 
-    t.datetime "updated_at"
149 
-    t.string   "mode",             default: "text", null: false
147 
+    t.datetime "created_at",                        null: false
148 
+    t.datetime "updated_at",                        null: false
149 
+    t.string   "mode",             default: "text", null: false, collation: "utf8_bin"
150 150 
   end
151 151
152 152 
   add_index "user_credentials", ["user_id", "credential_name"], name: "index_user_credentials_on_user_id_and_credential_name", unique: true, using: :btree
153 153
154 154 
   create_table "users", force: true do |t|
155 
-    t.string   "email",                  default: "",    null: false
156 
-    t.string   "encrypted_password",     default: "",    null: false
157 
-    t.string   "reset_password_token"
155 
+    t.string   "email",                              default: "",    null: false,                     collation: "utf8_bin"
156 
+    t.string   "encrypted_password",                 default: "",    null: false, charset: "ascii",   collation: "ascii_bin"
157 
+    t.string   "reset_password_token",                                                                collation: "utf8_bin"
158 158 
     t.datetime "reset_password_sent_at"
159 159 
     t.datetime "remember_created_at"
160 
-    t.integer  "sign_in_count",          default: 0
160 
+    t.integer  "sign_in_count",                      default: 0
161 161 
     t.datetime "current_sign_in_at"
162 162 
     t.datetime "last_sign_in_at"
163 163 
     t.string   "current_sign_in_ip"
164 164 
     t.string   "last_sign_in_ip"
165 
-    t.datetime "created_at"
166 
-    t.datetime "updated_at"
167 
-    t.boolean  "admin",                  default: false, null: false
168 
-    t.integer  "failed_attempts",        default: 0
165 
+    t.datetime "created_at",                                         null: false
166 
+    t.datetime "updated_at",                                         null: false
167 
+    t.boolean  "admin",                              default: false, null: false
168 
+    t.integer  "failed_attempts",                    default: 0
169 169 
     t.string   "unlock_token"
170 170 
     t.datetime "locked_at"
171 
-    t.string   "username",                               null: false
172 
-    t.string   "invitation_code",                        null: false
173 
-    t.integer  "scenario_count",         default: 0,     null: false
171 
+    t.string   "username",               limit: 191,                 null: false, charset: "utf8mb4", collation: "utf8mb4_unicode_ci"
172 
+    t.string   "invitation_code",                                    null: false,                     collation: "utf8_bin"
173 
+    t.integer  "scenario_count",                     default: 0,     null: false
174 174 
   end
175 175
176 176 
   add_index "users", ["email"], name: "index_users_on_email", unique: true, using: :btree

+ 110 - 0
lib/ar_mysql_column_charset.rb
View File 

@@ -0,0 +1,110 @@
1 
+require 'active_record'
2 
+
3 
+# Module#prepend support for Ruby 1.9
4 
+require 'prepend' unless Module.method_defined?(:prepend)
5 
+
6 
+module ActiveRecord::ConnectionAdapters
7 
+  class ColumnDefinition
8 
+    module CharsetSupport
9 
+      attr_accessor :charset, :collation
10 
+    end
11 
+
12 
+    prepend CharsetSupport
13 
+  end
14 
+
15 
+  class TableDefinition
16 
+    module CharsetSupport
17 
+      def new_column_definition(name, type, options)
18 
+        column = super
19 
+        column.charset   = options[:charset]
20 
+        column.collation = options[:collation]
21 
+        column
22 
+      end
23 
+    end
24 
+
25 
+    prepend CharsetSupport
26 
+  end
27 
+
28 
+  class AbstractMysqlAdapter
29 
+    module CharsetSupport
30 
+      def prepare_column_options(column, types)
31 
+        spec = super
32 
+        conn = ActiveRecord::Base.connection
33 
+        spec[:charset]   = column.charset.inspect if column.charset && column.charset != conn.charset
34 
+        spec[:collation] = column.collation.inspect if column.collation && column.collation != conn.collation
35 
+        spec
36 
+      end
37 
+
38 
+      def migration_keys
39 
+        super + [:charset, :collation]
40 
+      end
41 
+
42 
+      def utf8mb4_supported?
43 
+        if @utf8mb4_supported.nil?
44 
+          @utf8mb4_supported = !select("show character set like 'utf8mb4'").empty?
45 
+        else
46 
+          @utf8mb4_supported
47 
+        end
48 
+      end
49 
+
50 
+      def charset_collation(charset, collation)
51 
+        [charset, collation].map { |name|
52 
+          case name
53 
+          when nil
54 
+            nil
55 
+          when /\A(utf8mb4(_\w*)?)\z/
56 
+            if utf8mb4_supported?
57 
+              $1
58 
+            else
59 
+              "utf8#{$2}"
60 
+            end
61 
+          else
62 
+            name.to_s
63 
+          end
64 
+        }
65 
+      end
66 
+    end
67 
+
68 
+    prepend CharsetSupport
69 
+
70 
+    class SchemaCreation
71 
+      module CharsetSupport
72 
+        def column_options(o)
73 
+          column_options = super
74 
+          column_options[:charset]   = o.charset unless o.charset.nil?
75 
+          column_options[:collation] = o.collation unless o.collation.nil?
76 
+          column_options
77 
+        end
78 
+
79 
+        def add_column_options!(sql, options)
80 
+          charset, collation = @conn.charset_collation(options[:charset], options[:collation])
81 
+
82 
+          if charset
83 
+            sql << " CHARACTER SET #{charset}"
84 
+          end
85 
+
86 
+          if collation
87 
+            sql << " COLLATE #{collation}"
88 
+          end
89 
+
90 
+          super
91 
+        end
92 
+      end
93 
+
94 
+      prepend CharsetSupport
95 
+    end
96 
+
97 
+    class Column
98 
+      module CharsetSupport
99 
+        attr_reader :charset
100 
+
101 
+        def initialize(*args)
102 
+          super
103 
+          @charset = @collation[/\A[^_]+/] unless @collation.nil?
104 
+        end
105 
+      end
106 
+
107 
+      prepend CharsetSupport
108 
+    end
109 
+  end
110 
+end

+ 85 - 0
lib/prepend.rb
View File 

@@ -0,0 +1,85 @@
1 
+# Fake implementation of prepend(), which does not support overriding
2 
+# inherited methods nor methods that are formerly overridden by
3 
+# another invocation of prepend().
4 
+#
5 
+# Here's what <Original>.prepend(<Wrapper>) does:
6 
+#
7 
+# - Create an anonymous stub module (hereinafter <Stub>) and define
8 
+#   <Stub>#<method> that calls #<method>_without_<Wrapper> for each
9 
+#   instance method of <Wrapper>.
10 
+#
11 
+# - Rename <Original>#<method> to #<method>_without_<Wrapper> for each
12 
+#   instance method of <Wrapper>.
13 
+#
14 
+# - Include <Stub> and <Wrapper> into <Original> in that order.
15 
+#
16 
+# This way, a call of <Original>#<method> is dispatched to
17 
+# <Wrapper><method>, which may call super which is dispatched to
18 
+# <Stub>#<method>, which finally calls
19 
+# <Original>#<method>_without_<Wrapper> which is used to be called
20 
+# <Original>#<method>.
21 
+#
22 
+# Usage:
23 
+#
24 
+#     class Mechanize
25 
+#       # module with methods that overrides those of X
26 
+#       module Y
27 
+#       end
28 
+#
29 
+#       unless X.respond_to?(:prepend, true)
30 
+#         require 'mechanize/prependable'
31 
+#         X.extend(Prependable)
32 
+#       end
33 
+#
34 
+#       class X
35 
+#         prepend Y
36 
+#       end
37 
+#     end
38 
+class Module
39 
+  def prepend(mod)
40 
+    stub = Module.new
41 
+
42 
+    mod_id = (mod.name || 'Module__%d' % mod.object_id).gsub(/::/, '__')
43 
+
44 
+    mod.instance_methods.each { |name|
45 
+      method_defined?(name) or next
46 
+
47 
+      original = instance_method(name)
48 
+      next if original.owner != self
49 
+
50 
+      name = name.to_s
51 
+      name_without = name.sub(/(?=[?!=]?\z)/) { '_without_%s' % mod_id }
52 
+
53 
+      arity = original.arity
54 
+      arglist = (
55 
+        if arity >= 0
56 
+          (1..arity).map { |i| 'x%d' % i }
57 
+        else
58 
+          (1..(-arity - 1)).map { |i| 'x%d' % i } << '*a'
59 
+        end << '&b'
60 
+      ).join(', ')
61 
+
62 
+      if name.end_with?('=')
63 
+        stub.module_eval %{
64 
+          def #{name}(#{arglist})
65 
+            __send__(:#{name_without}, #{arglist})
66 
+          end
67 
+        }
68 
+      else
69 
+        stub.module_eval %{
70 
+          def #{name}(#{arglist})
71 
+            #{name_without}(#{arglist})
72 
+          end
73 
+        }
74 
+      end
75 
+      module_eval {
76 
+        alias_method name_without, name
77 
+        remove_method name
78 
+      }
79 
+    }
80 
+
81 
+    include stub
82 
+    include mod
83 
+  end
84 
+  private :prepend
85 
+end unless Module.method_defined?(:prepend)